import pandas as pd
import numpy as np


# 计算风险等级
def calculate_risk_grade(score):
    if score >= 17:
        return '高风险'
    elif 10 <= score < 17:
        return '中风险'
    else:
        return '低风险'


def calculate_risk(path):
    T = pd.read_excel(path, skiprows=1).ffill(axis=0)
    T.columns = ['cable_section_number',
                 'line_name',
                 'start_end',
                 'routine_test',
                 'avg_load_rate',
                 'max_load_rate',
                 'operation_years',
                 'three_year_faults',
                 'three_year_other_faults',
                 'cable_path',
                 'cable_number',
                 'path_environment',
                 'avg_cable_number',
                 'avg_load_volume',
                 'c1', 'c2', 'c3', 'c4', 'c5']

    # 初始化风险评分数组
    risk_scores = np.zeros(T.shape[0])

    # 投运前评估情况
    # 例行试验检测
    for i, row in T.iterrows():
        if row['routine_test'] == '合格':
            risk_scores[i] -= 2
        elif row['routine_test'] == '一般缺陷':
            risk_scores[i] += 1.27
        elif row['routine_test'] in ['严重缺陷', '严重缺陷以上', '严重缺陷及以上']:
            risk_scores[i] += 1.71

    # 近三年本体故障次数
    for i, row in T.iterrows():
        if row['three_year_faults'] == 1:
            risk_scores[i] += 2.4

    # 近三年接头故障次数
    for i, row in T.iterrows():
        if row['three_year_other_faults'] == 1:
            risk_scores[i] += 2.4

    # 通道环境
    for i, row in T.iterrows():
        if row['path_environment'] in ['优', '良好']:
            risk_scores[i] += 0
        elif row['path_environment'] == '一般':
            risk_scores[i] += 1.87
        elif row['path_environment'] == '差':
            risk_scores[i] += 2.1

    # 井内电缆数量
    for i, row in T.iterrows():
        if row['avg_cable_number'] == 1:
            risk_scores[i] += 1.67
        elif row['avg_cable_number'] == 2:
            risk_scores[i] += 2.01
        elif row['avg_cable_number'] == 3:
            risk_scores[i] += 1.4
        elif row['avg_cable_number'] == 5:
            risk_scores[i] += 1.63
        elif row['avg_cable_number'] == 6:
            risk_scores[i] += 1.78
        elif row['avg_cable_number'] in [8, 9]:
            risk_scores[i] += 2.01

    # 低压线路共通道
    for i, row in T.iterrows():
        if row['avg_load_volume'] == 1:
            risk_scores[i] += 2.01

    # 电缆头制作人员
    for i, row in T.iterrows():
        if row['c2'] == 0.01:
            risk_scores[i] -= 2
        elif row['c2'] == 0.12:
            risk_scores[i] -= 1

        if 0 <= row['c2'] <= 0.03:
            risk_scores[i] += 0
        elif 0.03 < row['c2'] <= 0.1:
            risk_scores[i] += 2.01
        elif 0.1 < row['c2'] <= 0.15:
            risk_scores[i] += 3.5
        elif row['c2'] > 0.15:
            risk_scores[i] += 2.01

    # 电缆井数量
    for i, row in T.iterrows():
        if row['cable_number'] in [6, 7, 8, 15, 16, 18]:
            risk_scores[i] -= 1.45
        elif 5 <= row['cable_number'] < 10:
            risk_scores[i] += 1.45
        elif 10 <= row['cable_number'] <= 15:
            risk_scores[i] += 6.58
        elif 15 < row['cable_number'] <= 20:
            risk_scores[i] += 4.03
        elif 20 < row['cable_number'] <= 25:
            risk_scores[i] += 6.03
        elif 25 < row['cable_number'] <= 30:
            risk_scores[i] += 6.03
        elif row['cable_number'] > 35:
            risk_scores[i] += 8

    # 电缆头投运时间
    for i, row in T.iterrows():
        if row['c4'] in [2, 4, 6, 7, 8]:
            risk_scores[i] -= 1
        elif 0 <= row['c4'] < 3:
            risk_scores[i] += 2.01
        elif 3 <= row['c4'] <= 5:
            risk_scores[i] += 2.01
        elif 5 < row['c4'] < 10:
            risk_scores[i] += 2.14
        elif row['c4'] >= 10:
            risk_scores[i] += 3.56

    # 交接试验检测情况
    for i, row in T.iterrows():
        if row['c5'] in ['优', '良']:
            risk_scores[i] += 0
        elif row['c5'] in ['合格', '一般', '一般缺陷']:
            risk_scores[i] += 1.83
        elif row['c5'] in ['严重缺陷', '严重缺陷以上', '严重缺陷及以上']:
            risk_scores[i] += 2.5

    # 年平均负载率
    for i, row in T.iterrows():
        if 0.4 < row['avg_load_rate'] <= 0.5:
            risk_scores[i] += 2
        elif 0.5 < row['avg_load_rate'] <= 0.6:
            risk_scores[i] += 1.66
        elif 0.6 < row['avg_load_rate'] <= 0.7:
            risk_scores[i] += 6.38

    # 年最大负载率
    for i, row in T.iterrows():
        if 0.1 < row['max_load_rate'] <= 0.2:
            risk_scores[i] -= 2
        elif 0.2 < row['max_load_rate'] <= 0.3:
            risk_scores[i] -= 2
        elif 0.4 < row['max_load_rate'] <= 0.5:
            risk_scores[i] -= 1.5
        elif 0.5 < row['max_load_rate'] <= 0.6:
            risk_scores[i] -= 1.3
        elif 0.6 < row['max_load_rate'] <= 0.7:
            risk_scores[i] -= 1
        elif 0.7 < row['max_load_rate'] <= 0.8:
            risk_scores[i] -= 1
        elif 0.3 < row['max_load_rate'] <= 0.4:
            risk_scores[i] += 1.71
        elif 0.8 < row['max_load_rate'] <= 0.9:
            risk_scores[i] += 1.6

    # 运行年限
    for i, row in T.iterrows():
        if row['operation_years'] in [2, 9, 11, 12, 18]:
            risk_scores[i] -= 1.5
        elif row['operation_years'] == 15:
            risk_scores[i] -= 2
        elif row['operation_years'] <= 3:
            risk_scores[i] += 0
        elif 3 < row['operation_years'] <= 8:
            risk_scores[i] += 3.3
        elif 8 < row['operation_years'] <= 15:
            risk_scores[i] += 4.3
        elif row['operation_years'] > 15:
            risk_scores[i] += 1.55

    # 确保风险评分不为负值
    risk_scores = np.maximum(risk_scores, 0)

    # 将风险评分添加到 DataFrame 中
    T['risk_score'] = risk_scores.round(2)
    T['risk_level'] = T['risk_score'].apply(calculate_risk_grade)

    return T


def calculate_cable_risk(risk_scores):
    # 计算公式
    product = 1
    for score in risk_scores:
        product *= (1 - score / 100)

    cable_risk = (1 - product) * 100
    return round(cable_risk, 2)


def calculate_cable_level(risk_score):
    """
    若电缆段风险评分>=43.73，则判定为高风险；>=20&<43.73，则判定为中风险；<20判定为低风险。
    """
    if risk_score >= 43.73:
        return "高风险"
    elif risk_score >= 20:
        return "中风险"
    return "低风险"


if __name__ == '__main__':
    content = pd.read_excel("D:\\Projects\\risk_api\\file\\线路起止点2.xlsx")
    content.columns = ['cable_section_number', 'line_name', 'start', 'end']
    d_list = content.to_dict('records')

    print(d_list)
